from db_conn.dbConn import conn_db,conn_db_engine
import pandas as pd
import time
conn1 = conn_db_engine('iot_local')
conn2 = conn_db_engine('ryerp_local')
connect = conn_db('iot_local')
# previous_time_point_str = previous_time_point.strftime('%Y-%m-%d %H:%M:%S')
sql1 = 'SELECT * FROM iotdemo WHERE receive_time <= %s'
# 定义previous_time_point为3天前的8点
previous_time_point = time.strftime('%Y-%m-%d 08:00:00', time.localtime(time.time() - 7 * 24 * 60 * 60))
print(previous_time_point)
# 使用Pandas的read_sql函数执行SQL语句并获取数据
df_iot = pd.read_sql(sql1, con=conn1, params=(previous_time_point,))
# 获取df_iot的最小id和最大id
min_id = df_iot['id'].min()
max_id = df_iot['id'].max()
# 定义SQL语句，获取iot_cache的最大id
query = 'SELECT MAX(id) AS max_id FROM iot_cache'
cursor = connect.cursor()
# 执行查询
cursor.execute(query)
# 获取查询结果
result = cursor.fetchone()
# 关闭connet和cursor
connect.close()
cursor.close()
# 获取最大ID
max_id_cache = result[0] if result[0] else None
# 打印这几个id
print(min_id, max_id, max_id_cache)
# 如果max_id_cache < max_id，则筛选出df_iot中id>max_id_cache的行
if max_id_cache < max_id:
    df_iot_new = df_iot[df_iot['id'] > max_id_cache]
    df_iot_new.to_sql('iot_cache', con=conn1, if_exists='append', index=False)
    df_cache = pd.read_sql(query, con=conn1)
    # 执行查询
    connect = conn_db('iot_local')
    cursor = connect.cursor()
    cursor.execute(query)
    # 获取查询结果
    result = cursor.fetchone()
    connect.close()
    cursor.close()
    # 获取最大ID
    max_id_cache = result[0] if result[0] else None
    # 打印这几个id
    print(max_id_cache)
    # 删除 iotdemo 中 id < max_id_cache 的数据
    delete_query = 'DELETE FROM iotdemo WHERE id <= %s'
    connect = conn_db('iot_local')
    cursor = connect.cursor()
    cursor.execute(delete_query, (max_id_cache,))
    connect.commit()
    connect.close()
    cursor.close()

# 将df_iot_new中的数据插入到iot_cache表中


